package dao;

import Utils.JDBCUtil;
import pojo.Order;
import pojo.QueryOrder;
import pojo.Shop;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderDao {
    public int insertOne(Order order) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "insert into `order`(id,course,account,year,month,day) values(?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, order.getId());
            preparedStatement.setString(2, order.getCourse());
            preparedStatement.setString(3, order.getAccount());
            preparedStatement.setInt(4, order.getYear());
            preparedStatement.setInt(5, order.getMonth());
            preparedStatement.setInt(6, order.getDay());

            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
        return count;
    }

    public int deleteOne(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "delete from `order` where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
        return count;
    }

    public int changeOne(int status, String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "update `order` set status=? where id=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, status);
            preparedStatement.setString(2, id);

            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
        return count;
    }

    public Order findOne(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Order order = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select * from `order` where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                order = new Order(id, resultSet.getString("course"), resultSet.getString("account"),
                        resultSet.getInt("status"), resultSet.getInt("year"), resultSet.getInt("month"),
                        resultSet.getInt("day"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return order;
    }

    public List<QueryOrder> findOrder(int page, int pageSize, String name, String account, int... val) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<QueryOrder> orders = new ArrayList<>();
        try {
            int status = val[0];
            int year = val[1];
            int month = val[2];
            int day = val[3];

            connection = JDBCUtil.getConnection();
            List<Integer> pars = new ArrayList<>();
            StringBuilder sql = new StringBuilder("select * from `order` join course on `order`.course = course.id where 1=1");
            if (name != null && !name.equals("")) {
                sql.append(" and course.name like ?");
            }
            if (account != null && !account.equals("")) {
                sql.append(" and account = ?");
            }
            if (status != -1) {
                sql.append(" and `order`.status = ?");
                pars.add(status);
            }
            if (year != -1) {
                sql.append(" and `order`.year = ?");
                pars.add(year);
            }
            if (month != -1) {
                sql.append(" and `order`.month = ?");
                pars.add(month);
            }
            if (day != -1) {
                sql.append(" and `order`.day = ?");
                pars.add(day);
            }

            sql.append(" order by `order`.id limit ?,?;");
            preparedStatement = connection.prepareStatement(sql.toString());
            int index = 1;
            if (name != null && !name.equals("")) {
                preparedStatement.setString(index++, "%" + name + "%");
            }
            if (account != null && !account.equals("")) {
                preparedStatement.setString(index++, account);
            }
            for (int par : pars) {
                preparedStatement.setInt(index++, par);
            }
            int start = (page - 1) * pageSize;
            preparedStatement.setInt(index++, start);
            preparedStatement.setInt(index, pageSize);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                orders.add(new QueryOrder(resultSet.getString("order.id"), resultSet.getString("name"), resultSet.getString("account"),
                        resultSet.getInt("price"), resultSet.getInt("year"), resultSet.getInt("month"),
                        resultSet.getInt("day"), resultSet.getInt("status")));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return orders;
    }

    public int countOrder(String name, String account, int... val) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            int status = val[0];
            int year = val[1];
            int month = val[2];
            int day = val[3];

            connection = JDBCUtil.getConnection();
            List<Integer> pars = new ArrayList<>();
            StringBuilder sql = new StringBuilder()
                    .append("select count(*) as count from `order`,course")
                    .append(" where `order`.course=course.id");
            if (name != null && !name.equals("")) {
                sql.append(" and name like ?");
            }
            if (account != null && !account.equals("")) {
                sql.append(" and account=?");
            }
            if (status != -1) {
                sql.append(" and status=?");
                pars.add(status);
            }
            if (year != -1) {
                sql.append(" and year=?");
                pars.add(year);
            }
            if (month != -1) {
                sql.append(" and month=?");
                pars.add(month);
            }
            if (day != -1) {
                sql.append(" and day=?;");
                pars.add(day);
            }
            preparedStatement = connection.prepareStatement(sql.toString());
            int index = 1;
            if (name != null && !name.equals("")) {
                preparedStatement.setString(index++, " %" + name + "%");
            }
            if (account != null && !account.equals("")) {
                preparedStatement.setString(index++, account);
            }
            for (int par : pars) {
                preparedStatement.setInt(index++, par);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return count;
    }
}